package com.qf.work20250106;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author zdl
 * @date 2025/1/6
 */
public class Demo1 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("=================学生信息管理系统=================");
            System.out.println(" 1- 添加学生信息");
            System.out.println(" 2- 修改学生信息");
            System.out.println(" 3- 删除学生信息");
            System.out.println(" 4- 查看学生信息");
            System.out.println(" 5- 退出系统");
            System.out.println("请输入你的选择:");
            int choice = sc.nextInt();
            ui(choice);
        }
    }

    public static void ui(int choice) throws SQLException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        int id;
        String name;
        int age;
        String gender;
        String className;
        switch (choice) {
            case 1:
                System.out.println("请输入添加学生id:");
                id = sc.nextInt();
                if (!selectOne(id)) {
                    System.out.println("请输入添加学生name:");
                    name = sc.next();
                    System.out.println("请输入添加学生age:");
                    age = sc.nextInt();
                    System.out.println("请输入添加学生gender(男|女):");
                    gender = sc.next();
                    System.out.println("请输入添加学生class:");
                    className = sc.next();
                    add(new Student(id, name, age, gender, className));
                } else {
                    System.out.println("添加的学生id:" + id + "已存在，无法添加!");
                }
                break;
            case 2:
                System.out.println("请输入修改学生id:");
                id = sc.nextInt();
                if (selectOne(id)) {
                    System.out.println("请输入修改学生name:");
                    name = sc.next();
                    System.out.println("请输入修改学生age:");
                    age = sc.nextInt();
                    System.out.println("请输入修改学生gender(男|女):");
                    gender = sc.next();
                    System.out.println("请输入修改学生class:");
                    className = sc.next();
                    updateOne(new Student(id, name, age, gender, className));
                } else {
                    System.out.println("学生id:" + id + "不存在，无法修改!");
                }
                break;
            case 3:
                System.out.println("请输入删除学生信息的id:");
                id = sc.nextInt();
                if (selectOne(id)) {
                    delOne(id);
                } else {
                    System.out.println("该学生信息id:" + id + "不存在，无法删除");
                }
                break;
            case 4:
                List<Student> students = queryAll();
                for (Student student : students) {
                    if(student != null){
                        System.out.println(student);
                    }
                }
                break;
            case 5:
                System.exit(0);
            default:
                System.out.println("输入正确的选项!");
        }
    }

    public static List<Student> queryAll() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_3?useSSL=false", "root", "123456");
        String sql = "select * from students";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Student> list = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String gender = rs.getString("gender");
            String s = rs.getString("class");
            list.add(new Student(id, name, age, gender, s));
        }
        return list;
    }

    public static boolean selectOne(int id) throws SQLException {
        QueryRunner rq = new QueryRunner();
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_3?useSSL=false", "root", "123456");
        String sql = "select id,name,age,gender,class as className from students where id = ?";
        Student student = rq.query(conn, sql, id, new BeanHandler<>(Student.class));
        return student != null ? true : false;
    }

    public static void add(Student student) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_3?useSSL=false", "root", "123456");
        String sql = "insert into students (id,name,age,gender,class) values (?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, student.getId());
        ps.setString(2, student.getName());
        ps.setInt(3, student.getAge());
        ps.setString(4, student.getGender());
        ps.setString(5, student.getClassName());
        int i = ps.executeUpdate();
        System.out.println("成功添加一条学生数据");
    }

    public static void delOne(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_3?useSSL=false", "root", "123456");
        String query = "select * from students where id = ?";
        Student queryId = queryRunner.query(conn, query, id, new BeanHandler<>(Student.class));
        if (queryId != null) {
            String sql = "delete from students where id = ?";
            int update = queryRunner.update(conn, sql, id);
            System.out.println("成功删除" + update + "条数据!");
        } else {
            System.out.println("输入的id:" + id + "不存在,无法删除");
        }
    }

    public static void updateOne(Student student) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_3?useSSL=false", "root", "123456");
        String query = "select * from students where id = ?";
        Student queryId = queryRunner.query(conn, query, student.getId(), new BeanHandler<>(Student.class));
        if (queryId != null) {
            String sql = "update students set name = ? , age = ? , gender = ?, class = ? where id = ?";
            Object[] params = {student.getName(), student.getAge(), student.getGender(), student.getClassName(), student.getId()};
            int update = queryRunner.update(conn, sql, params);
            System.out.println("成功修改" + update + "条数据!");
        } else {
            System.out.println("输入的id:" + student.getId() + "不存在,无法修改");
        }


    }
}
